﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using OpenSISDataConnection;
using OpenSISPeriodBusinessLayer;

namespace OpenSISPeriodDataLayer
{

    public class PeriodDL
    {
        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

        public System.Data.DataTable SchoolPeriodDetails(PeriodBL Period)
        {
            DataTable dt = null;
            string length = "";
            if (Period.Length == 0)
            {
                length = "";
            }
            else
            {
                length = Period.Length.ToString();
            }
            dt = DataConnection.ConnectionPath.Getdata("select * from schools_period where sch_id=" + Period.SchId + " and title like N'" + Period.Title + "%' and short_name like N'" + Period.ShortName + "%' and short_order like  N'" + Period.ShortOrder + "%' and length like N'" + length + "%' and block like N'" + Period.Block + "%' and use_atted like N'" + Period.UseAtted + "%' order by short_order").Tables[0];
            return dt;
        }

        public void AddSchoolPeriod(PeriodBL Period)
        {
            if (server == "MSSQLServer")
            {
            DataSet ds = null;
            DataSet dsShortOrder = null;
            ds = DataConnection.ConnectionPath.Getdata("exec getMax sch_period_id,schools_period");

            dsShortOrder = DataConnection.ConnectionPath.Getdata("select isnull(max(short_order),0) + 1 as short_order from schools_period where sch_id='" + Period.SchId + "'");

            String strAddSchoolPeriod = "INSERT into schools_period(sch_period_id,sch_id,title,short_name,short_order,length,block,use_atted,start_time,end_time) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + Period.SchId + ", N'" + Period.Title.Replace("'", "''") + "',N'" + Period.ShortName.Replace("'", "''") + "',N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "'," + Period.Length + ",N'" + Period.Block.Replace("'", "''") + "',N'" + Period.UseAtted.Replace("'", "''") + "',N'" + Period.StartTime.Replace("'", "''") + "',N'" + Period.EndTime.Replace("'", "''") + "')";

            DataConnection.ConnectionPath.ExecuteCommand(strAddSchoolPeriod);
            }

            if (server == "MySQL")
            {
                DataSet ds = null;
                DataSet dsShortOrder = null;
                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('sch_period_id','schools_period')");
                dsShortOrder = DataConnection.ConnectionPath.Getdata("select IFNULL(max(short_order),0) + 1 as short_order from schools_period where sch_id='" + Period.SchId + "'");
                String strAddSchoolPeriod = "INSERT into schools_period(sch_period_id,sch_id,title,short_name,short_order,length,block,use_atted,start_time,end_time) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + Period.SchId + ", N'" + Period.Title.Replace("'", "''") + "',N'" + Period.ShortName.Replace("'", "''") + "',N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "'," + Period.Length + ",N'" + Period.Block.Replace("'", "''") + "',N'" + Period.UseAtted.Replace("'", "''") + "',N'" + Period.StartTime.Replace("'", "''") + "',N'" + Period.EndTime.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strAddSchoolPeriod);
            }
        }

        public void UpdateSchoolPeriod(PeriodBL Period)
        {
          if (server == "MSSQLServer")
           {
                string strUpdatePeriod = "update schools_period set title= N'" + Period.Title.Replace("'", "''") + "',short_name= N'" + Period.ShortName.Replace("'", "''") + "',length=" + Period.Length + ",block= N'" + Period.Block.Replace("'", "''") + "',use_atted= N'" + Period.UseAtted + "',start_time= N'" + Period.StartTime.Replace("'", "''") + "',end_time= N'" + Period.EndTime.Replace("'", "''") + "' where sch_period_id=" + Period.SchPeriodId + "";

                DataConnection.ConnectionPath.ExecuteCommand(strUpdatePeriod);
           }

          if (server == "MySQL")
          {
              string strUpdatePeriod = "update schools_period set title= N'" + Period.Title.Replace("'", "''") + "',short_name= N'" + Period.ShortName.Replace("'", "''") + "',length=" + Period.Length + ",block= N'" + Period.Block.Replace("'", "''") + "',use_atted= N'" + Period.UseAtted + "',start_time= N'" + Period.StartTime.Replace("'", "''") + "',end_time= N'" + Period.EndTime.Replace("'", "''") + "' where sch_period_id=" + Period.SchPeriodId + "";

              DataConnection.ConnectionPath.ExecuteCommand(strUpdatePeriod);

          }

        }

        public void UpdateSchoolPeriodShortOrder(PeriodBL Period)
        {
            string strUpdatePeriod = "update schools_period set short_order= N'" + Period.ShortOrder.Replace("'", "''") + "' where sch_period_id=" + Period.SchPeriodId + "";

            DataConnection.ConnectionPath.ExecuteCommand(strUpdatePeriod);

        }

        public void DeleteSchoolPeriod(PeriodBL Period)
        {

            string strSchoolPeriodDelete = "delete from schools_period where sch_period_id=" + Period.SchPeriodId + "";
            DataConnection.ConnectionPath.ExecuteCommand(strSchoolPeriodDelete);

        }
    }
}